package cn.rulian.demo.one.controller;

import java.io.ByteArrayInputStream;
import java.io.File;
import java.io.FileInputStream;
import java.text.SimpleDateFormat;
import java.util.ArrayList;
import java.util.HashMap;
import java.util.List;
import java.util.Map;

import javax.servlet.http.HttpServletResponse;

import jxl.Workbook;
import jxl.format.Alignment;
import jxl.format.Border;
import jxl.format.BorderLineStyle;
import jxl.format.Colour;
import jxl.format.VerticalAlignment;
import jxl.write.Label;
import jxl.write.WritableCellFormat;
import jxl.write.WritableFont;

import org.springframework.beans.factory.annotation.Autowired;
import org.springframework.stereotype.Controller;
import org.springframework.ui.Model;
import org.springframework.web.bind.annotation.RequestMapping;
import org.springframework.web.bind.annotation.ResponseBody;

import cn.rulian.base.auth.ann.Access;
import cn.rulian.base.common.dto.ExcelBook;
import cn.rulian.base.common.dto.ExcelSheet;
import cn.rulian.base.common.dto.Message;
import cn.rulian.base.common.util.Num;
import cn.rulian.base.common.vo.Result;
import cn.rulian.demo.one.bo.One;
import cn.rulian.demo.one.service.OneService;
import cn.rulian.demo.one.vo.OneVo;

/**
 * 全能示例 Controller
 */
@Access(authCode="one")
@Controller
@RequestMapping("/demo/one/one")
public class OneController {
	
	@Autowired
	private OneService service;
	
	private final String path="demo/one/one_"; //页面基本路径

	/**
	 * 页面基本路径
	 */
	private String getPath()
	{
		return path;
	}
	
	/**
	 * 列表页面
	 */
	@RequestMapping("/listPage")
	public String listPage() {
		return this.getPath()+"list";
	}

	/**
	 * 查询
	 */
	@RequestMapping("/listData")
	@ResponseBody
	public Result listData(OneVo vo) {
		return service.query(vo);
	}

	/**
	 * 增加页面
	 */
	@Access(authCode="one_add")
	@RequestMapping("/addPage")
	public String addPage() {
		return this.getPath()+"add";
	}

	/**
	 * 增加
	 */
	@Access(authCode="one_add")
	@RequestMapping("/add")
	@ResponseBody
	public Message add(OneVo vo) {
		Message rm = new Message();
		One bo = vo.getObj();
		try {
			
			service.save(bo);
			
			rm.setMsg("保存成功！");
			rm.setCode(Message._SUCCESS);
		} catch (Exception e) {
			rm.setMsg("保存失败，异常信息：<br/>" + e.toString());
			rm.setCode(Message._ERROR);
			e.printStackTrace();
		}
		return rm;
	}

	/**
	 * 修改页面
	 */
	@Access(authCode="one_update")
	@RequestMapping("/updatePage")
	public String updatePage(Model model, Long id) {
		model.addAttribute("bo", service.findOne(id));
		return this.getPath()+"update";
	}

	/**
	 * 修改
	 */
	@Access(authCode="one_update")
	@RequestMapping("/update")
	@ResponseBody
	public Message update(OneVo vo,Long id) {
		Message rm = new Message();
		try {
			One dto = vo.getObj();
			One bo = service.findOne(id);
			
			bo.setCode(dto.getCode());
			bo.setName(dto.getName());
			bo.setCity(dto.getCity());
			bo.setTea(dto.getTea());
			bo.setHobby(dto.getHobby());
			bo.setOneSort(dto.getOneSort());
			bo.setRemark(dto.getRemark());
			bo.setIntNum1(dto.getIntNum1());
			bo.setIntNum2(dto.getIntNum2());
			bo.setDoubleNum1(dto.getDoubleNum1());
			bo.setDoubleNum2(dto.getDoubleNum2());
			bo.setBoolean1(dto.getBoolean1());
			bo.setBoolean2(dto.getBoolean2());
			bo.setTheDate1(dto.getTheDate1());
			bo.setTheDate2(dto.getTheDate2());
			bo.setTheTime1(dto.getTheTime1());
			bo.setTheTime2(dto.getTheTime2());
			
			service.save(bo);
			
			rm.setMsg("保存成功！");
			rm.setCode(Message._SUCCESS);
		} catch (Exception e) {
			rm.setMsg("保存失败，异常信息：<br/>" + e.toString());
			rm.setCode(Message._ERROR);
			e.printStackTrace();
		}
		return rm;
	}

	/**
	 * 删除
	 */
	@Access(authCode="one_del")
	@RequestMapping("/delete")
	@ResponseBody
	public Message delete(Long ids[]) {
		Message rm = new Message();
		try {
			for(Long id:ids)
			{
				service.delete(id);
			}
			
			rm.setMsg("删除成功！");
			rm.setCode(Message._SUCCESS);
		} catch (Exception e) {
			rm.setMsg("删除失败，异常信息：<br/>" + e.toString());
			rm.setCode(Message._ERROR);
			e.printStackTrace();
		}
		return rm;
	}
	
	/**
	 * 详情页面
	 */
	@RequestMapping("/detailPage")
	public String detailPage(Model model, Long id) {
		model.addAttribute("bo", service.findOne(id));
		return this.getPath()+"detail";
	}

	/**
	 * 导出Excel
	 * @throws Exception 
	 */
	@RequestMapping("/exportExcel")
	public void exportExcel(HttpServletResponse response,OneVo vo,String type) throws Exception {
		
		
		File file = new File("aaa.xls");
		jxl.write.WritableWorkbook book = Workbook.createWorkbook(file); //工作薄

		List<One> allDataList=service.QueryExcelData(vo); //取得导出数据
		ExcelBook<One> excelBook = this.buildExcelData(allDataList, type); //按维度进行数据加工
		String bookName = excelBook.getName(); //Excel文件名称
		List<ExcelSheet<One>> sheetList = excelBook.getSheetList(); //各Sheet列表
		if(sheetList!=null && sheetList.size() > 0)
		{
			for(ExcelSheet<One> excelSheet:sheetList)
			{
				String sheetName = excelSheet.getName(); //名称
				List<One> oneList = excelSheet.getDataList(); //数据列表
				
				jxl.write.WritableSheet sheet = book.createSheet(sheetName, 0); //工作表
				
				//设置行高
				sheet.getSettings().setDefaultRowHeight(350); //设置所有行默认行高
				sheet.setRowView(0, 700);//第1行 高度
				sheet.setRowView(1, 400);//第2行 高度
				
				//设置个别列宽
				sheet.setColumnView(5, 14);//宽度设置
				sheet.setColumnView(13, 12);//宽度设置
				sheet.setColumnView(14, 12);//宽度设置
				sheet.setColumnView(15, 16);//宽度设置
				sheet.setColumnView(16, 16);//宽度设置
				sheet.setColumnView(17, 16);//宽度设置
				sheet.setColumnView(18, 18);//宽度设置
				
				//定义格式
				//主标题格式
				WritableCellFormat formatterTitle= new WritableCellFormat(); //标题格式
				formatterTitle.setAlignment(Alignment.CENTRE);//水平对齐
				formatterTitle.setVerticalAlignment(VerticalAlignment.CENTRE);//垂直对齐
				//WritableFont font = new WritableFont(WritableFont.createFont("ＭＳ Ｐゴシック"), 18, WritableFont.BOLD, false); //黑体
				WritableFont font = new WritableFont(WritableFont.TAHOMA, 18, WritableFont.BOLD, false);
				formatterTitle.setFont(font);
				
				//列标题格式
				WritableCellFormat formatterColumnTitle= new WritableCellFormat(); //列标题格式
				formatterColumnTitle.setAlignment(Alignment.CENTRE);//水平对齐
				formatterColumnTitle.setVerticalAlignment(VerticalAlignment.CENTRE);//垂直对齐
				formatterColumnTitle .setBackground(Colour.GRAY_25);//背景色
				formatterColumnTitle.setBorder(Border.ALL, BorderLineStyle.THIN);//边框线  所有边框 细线

				//普通内容格式
				WritableCellFormat formatterCon= new WritableCellFormat(); //内容格式
				formatterCon.setAlignment(Alignment.CENTRE);//水平对齐
				formatterCon.setVerticalAlignment(VerticalAlignment.CENTRE);//垂直对齐
				formatterCon.setBorder(Border.ALL, BorderLineStyle.THIN);//边框线  所有边框 细线
				
				//合计行格式
				WritableCellFormat formatterSum= new WritableCellFormat(); //合计格式
				formatterSum.setAlignment(Alignment.CENTRE);//水平对齐
				formatterSum.setVerticalAlignment(VerticalAlignment.CENTRE);//垂直对齐
				formatterSum.setBorder(Border.ALL, BorderLineStyle.THIN);//边框线  所有边框 细线
				WritableFont font_sum = new WritableFont(WritableFont.TAHOMA, 10, WritableFont.BOLD, false);
				formatterSum.setFont(font_sum);
				
				//输出标题
				sheet.mergeCells(0, 0, 18, 0); //合并单元格（标题）
				sheet.addCell(creatLabel(0,0,"全能示例（"+sheetName+"）",formatterTitle));

				//输出列标题
				int rowNum=1; //行号
				int columnNum=0; //列号
				sheet.addCell(creatLabel(rowNum,columnNum++,"序号",formatterColumnTitle));
				sheet.addCell(creatLabel(rowNum,columnNum++,"编号",formatterColumnTitle));
				sheet.addCell(creatLabel(rowNum,columnNum++,"名称",formatterColumnTitle));
				sheet.addCell(creatLabel(rowNum,columnNum++,"城市",formatterColumnTitle));
				sheet.addCell(creatLabel(rowNum,columnNum++,"茶品",formatterColumnTitle));
				sheet.addCell(creatLabel(rowNum,columnNum++,"分类",formatterColumnTitle));
				sheet.addCell(creatLabel(rowNum,columnNum++,"爱好",formatterColumnTitle));
				sheet.addCell(creatLabel(rowNum,columnNum++,"整数1",formatterColumnTitle));
				sheet.addCell(creatLabel(rowNum,columnNum++,"整数2",formatterColumnTitle));
				sheet.addCell(creatLabel(rowNum,columnNum++,"实数1",formatterColumnTitle));
				sheet.addCell(creatLabel(rowNum,columnNum++,"实数2",formatterColumnTitle));
				sheet.addCell(creatLabel(rowNum,columnNum++,"布尔1",formatterColumnTitle));
				sheet.addCell(creatLabel(rowNum,columnNum++,"布尔2",formatterColumnTitle));
				sheet.addCell(creatLabel(rowNum,columnNum++,"日期1",formatterColumnTitle));
				sheet.addCell(creatLabel(rowNum,columnNum++,"日期2",formatterColumnTitle));
				sheet.addCell(creatLabel(rowNum,columnNum++,"时间1",formatterColumnTitle));
				sheet.addCell(creatLabel(rowNum,columnNum++,"时间2",formatterColumnTitle));
				sheet.addCell(creatLabel(rowNum,columnNum++,"备注",formatterColumnTitle));
				sheet.addCell(creatLabel(rowNum,columnNum++,"增加时间",formatterColumnTitle));
				
				//合计项
				int intNum1Sum = 0; // 整数1
				int intNum2Sum = 0; // 整数2
				double doubleNum1Sum = 0; // 实数1
				double doubleNum2Sum = 0; // 实数2
			
				if(oneList != null && oneList.size() > 0)
				{
					SimpleDateFormat formatterDate = new SimpleDateFormat("yyyy-MM-dd"); //日期格式化
					SimpleDateFormat formatterTime = new SimpleDateFormat("yyyy-MM-dd HH:mm"); //时间格式化
					SimpleDateFormat formatterTime2 = new SimpleDateFormat("yyyy-MM-dd HH:mm:ss"); //时间格式化2
					
					//展示数据
					String codeStr = ""; //编号
					String nameStr = ""; //名称
					String cityStr = ""; //城市
					String teaStr = ""; //茶品
					String sortStr = ""; //类型
					String hobbyStr = ""; //爱好
					String intNum1Str = ""; //整数1
					String intNum2Str = ""; //整数2
					String doubleNum1Str = ""; //实数1
					String doubleNum2Str = ""; //实数2
					String boolean1Str = ""; //布尔1
					String boolean2Str = ""; //布尔2
					String theDate1Str = ""; //日期1
					String theDate2Str = ""; //日期2
					String theTime1Str = ""; //时间1
					String theTime2Str = ""; //时间2
					String remarkStr = ""; //备注
					String addTimeStr = ""; //增加时间
					
					for(int i = 0; i < oneList.size(); i++)
			    	{
						One bo = oneList.get(i); 
						rowNum++;
						columnNum=0;
						
						//展示数据初始化
						codeStr = ""; //编号
						nameStr = ""; //名称
						cityStr = ""; //城市
						teaStr = ""; //茶品
						sortStr = ""; //类型
						hobbyStr = ""; //爱好
						intNum1Str = ""; //整数1
						intNum2Str = ""; //整数2
						doubleNum1Str = ""; //实数1
						doubleNum2Str = ""; //实数2
						boolean1Str = ""; //布尔1
						boolean2Str = ""; //布尔2
						theDate1Str = ""; //日期1
						theDate2Str = ""; //日期2
						theTime1Str = ""; //时间1
						theTime2Str = ""; //时间2
						remarkStr = ""; //备注
						addTimeStr = ""; //增加时间
						
						//展示数据赋值
						if(bo.getCode() != null){	codeStr = bo.getCode();	} //编号
						if(bo.getName() != null){	nameStr = bo.getName();	} //名称
						if(bo.getCity() != null){	cityStr = bo.getCity();	} //城市
						if(bo.getTea() != null){	teaStr = bo.getTea();	} //城市
						if(bo.getOneSort() != null){	sortStr = bo.getOneSort().getName();	} //类型
						if(bo.getHobby() != null){	hobbyStr = bo.getHobby();	} //爱好
						if(bo.getIntNum1() != null){	intNum1Str = bo.getIntNum1().toString();	} //整数1
						if(bo.getIntNum2() != null){	intNum2Str = bo.getIntNum2().toString();	} //整数2
						if(bo.getDoubleNum1()!= null){	doubleNum1Str = bo.getDoubleNum1().toString();	} //实数1
						if(bo.getDoubleNum2() != null){	doubleNum2Str = bo.getDoubleNum2().toString();	} //实数2
						if(bo.getBoolean1()!= null){	boolean1Str = bo.getBoolean1()?"是":"否";	} //布尔1
						if(bo.getBoolean2() != null){	boolean2Str = bo.getBoolean2()?"开":"关";		} //布尔2
						if(bo.getTheDate1() != null){	theDate1Str = formatterDate.format(bo.getTheDate1());	} //日期1
						if(bo.getTheDate2() != null){	theDate2Str = formatterDate.format(bo.getTheDate2());	} //日期2
						if(bo.getTheTime1() != null){	theTime1Str = formatterTime.format(bo.getTheTime1());	} //时间1
						if(bo.getTheTime2() != null){	theTime2Str = formatterTime.format(bo.getTheTime2());	} //时间2
						if(bo.getRemark()!= null){	remarkStr = bo.getRemark();	} //备注
						if(bo.getAddTime() != null){	addTimeStr = formatterTime2.format(bo.getAddTime());	} //增加时间
						
						//输出一行数据
			    		sheet.addCell(creatLabel(rowNum,columnNum++,(i+1)+"",formatterCon)); 
			    		sheet.addCell(creatLabel(rowNum,columnNum++,codeStr ,formatterCon)); //编号
			    		sheet.addCell(creatLabel(rowNum,columnNum++,nameStr ,formatterCon)); //名称
			    		sheet.addCell(creatLabel(rowNum,columnNum++,cityStr ,formatterCon)); //城市
			    		sheet.addCell(creatLabel(rowNum,columnNum++,teaStr ,formatterCon)); //茶品
			    		sheet.addCell(creatLabel(rowNum,columnNum++,sortStr ,formatterCon)); //分类
			    		sheet.addCell(creatLabel(rowNum,columnNum++,hobbyStr ,formatterCon)); //爱好
			    		sheet.addCell(creatLabel(rowNum,columnNum++,intNum1Str ,formatterCon)); //整数1
			    		sheet.addCell(creatLabel(rowNum,columnNum++,intNum2Str ,formatterCon)); //整数2
			    		sheet.addCell(creatLabel(rowNum,columnNum++,doubleNum1Str ,formatterCon)); //实数1
			    		sheet.addCell(creatLabel(rowNum,columnNum++,doubleNum2Str ,formatterCon)); //实数2
			    		sheet.addCell(creatLabel(rowNum,columnNum++,boolean1Str ,formatterCon)); //布尔1
			    		sheet.addCell(creatLabel(rowNum,columnNum++,boolean2Str ,formatterCon)); //布尔2
			    		sheet.addCell(creatLabel(rowNum,columnNum++,theDate1Str ,formatterCon)); //日期1
			    		sheet.addCell(creatLabel(rowNum,columnNum++,theDate2Str ,formatterCon)); //日期2
			    		sheet.addCell(creatLabel(rowNum,columnNum++,theTime1Str ,formatterCon)); //时间1
			    		sheet.addCell(creatLabel(rowNum,columnNum++,theTime2Str ,formatterCon)); //时间2
			    		sheet.addCell(creatLabel(rowNum,columnNum++,remarkStr,formatterCon)); //备注
			    		sheet.addCell(creatLabel(rowNum,columnNum++,addTimeStr,formatterCon)); //增加时间
			    		
			    		//合计项累加
						if(bo.getIntNum1()!=null)
						{
							intNum1Sum += bo.getIntNum1();
						}
						if(bo.getIntNum2()!=null)
						{
							intNum2Sum += bo.getIntNum2();
						}
						if(bo.getDoubleNum1()!=null)
						{
							doubleNum1Sum = Num.add(doubleNum1Sum, bo.getDoubleNum1());
						}
						if(bo.getDoubleNum2()!=null)
						{
							doubleNum2Sum = Num.add(doubleNum2Sum, bo.getDoubleNum2());
						}
			    	}
					
					//表格增加合计项
					rowNum++;
					columnNum = 0;
					sheet.setRowView(rowNum, 400);//合计行 高度
		    		
		    		sheet.addCell(creatLabel(rowNum,columnNum++,"",formatterSum)); 
		    		sheet.addCell(creatLabel(rowNum,columnNum++,"" ,formatterSum)); //编号
		    		sheet.addCell(creatLabel(rowNum,columnNum++,"" ,formatterSum)); //名称
		    		sheet.addCell(creatLabel(rowNum,columnNum++,"" ,formatterSum)); //城市
		    		sheet.addCell(creatLabel(rowNum,columnNum++,"" ,formatterSum)); //茶品
		    		sheet.addCell(creatLabel(rowNum,columnNum++,"" ,formatterSum)); //类型
		    		sheet.addCell(creatLabel(rowNum,columnNum++,"合计" ,formatterSum)); //爱好
		    		sheet.addCell(creatLabel(rowNum,columnNum++,intNum1Sum+"" ,formatterSum)); //整数1
		    		sheet.addCell(creatLabel(rowNum,columnNum++,intNum2Sum+"" ,formatterSum)); //整数2
		    		sheet.addCell(creatLabel(rowNum,columnNum++,doubleNum1Sum+"" ,formatterSum)); //实数1
		    		sheet.addCell(creatLabel(rowNum,columnNum++,doubleNum2Sum+"" ,formatterSum)); //实数2
		    		sheet.addCell(creatLabel(rowNum,columnNum++,"" ,formatterSum)); //布尔1
		    		sheet.addCell(creatLabel(rowNum,columnNum++,"" ,formatterSum)); //布尔2
		    		sheet.addCell(creatLabel(rowNum,columnNum++,"" ,formatterSum)); //日期1
		    		sheet.addCell(creatLabel(rowNum,columnNum++,"" ,formatterSum)); //日期2
		    		sheet.addCell(creatLabel(rowNum,columnNum++,"" ,formatterSum)); //时间1
		    		sheet.addCell(creatLabel(rowNum,columnNum++,"" ,formatterSum)); //时间2
		    		sheet.addCell(creatLabel(rowNum,columnNum++,"",formatterSum)); //备注
		    		sheet.addCell(creatLabel(rowNum,columnNum++,"",formatterSum)); //增加时间
				}
			}
		}
		
		//-------------------------------
		book.write();
		book.close();

		FileInputStream f = new FileInputStream(file);
		byte[] fb = new byte[f.available()];
		f.read(fb);
		response.setHeader("Content-disposition", "attachment; filename=" + new String((bookName + ".xls").getBytes("gb2312"), "iso8859-1"));
		ByteArrayInputStream bais = new ByteArrayInputStream(fb);
		int b;
		while ((b = bais.read()) != -1)
		{
			response.getOutputStream().write(b);
		}
		response.getOutputStream().flush();
		f.close();
		
	}
	
	private Label creatLabel(int rowNum,int columnNum,String con,WritableCellFormat formatter) //行，列，内容 ，格式化
	{  
		 return new Label(columnNum, rowNum, con,formatter);   
	}  
	
	/**
	 * 从不同维度，构建Excel导出所需要的数据
	 */
	private ExcelBook<One> buildExcelData(List<One> list,String exportType)
	{
		ExcelBook<One> book = new ExcelBook<One>();
		String type1="type"; //维度：类型
		String type2="city"; //维度：城市
		
		//确定Excel文件名
		if(type1.equals(exportType))
		{//部门维度
			book.setName("全能示例（分类）");
		}
		else if(type2.equals(exportType))
		{//车辆维度
			book.setName("全能示例（分城市）");
		}
		else
		{//不分表
			book.setName("全能示例");
		}

		//根据维度（sheetName）不同，对数据分表
		Map<String,List<One>> map =new HashMap<String,List<One>>(); 
		String sheetName = null;
		for(One bo:list)
		{
			if(type1.equals(exportType))
			{//部门维度
				if(bo.getOneSort()!=null)
				{
					sheetName = bo.getOneSort().getName();
				}
				else
				{
					sheetName="无类型";
				}
			}
			else if(type2.equals(exportType))
			{//车辆维度
				if(bo.getCity()!=null)
				{
					sheetName = bo.getCity();
				}
				else
				{
					sheetName="无城市";
				}
			}
			
			else
			{//不分表
				sheetName="全部";
			}
			
			List<One> dataList = map.get(sheetName);
			if(dataList==null)
			{
				dataList = new ArrayList<>();
			}
			dataList.add(bo);
			map.put(sheetName, dataList);
		}
		
		//转换Map,得到数据表列表
		List<ExcelSheet<One>> sheetList = new ArrayList<>();
		for (Map.Entry<String, List<One>> m : map.entrySet()) 
		{
			ExcelSheet<One> sheet = new ExcelSheet<One>();
			sheet.setName(m.getKey());
			sheet.setDataList(m.getValue());
			sheetList.add(sheet);
		} 
		
		if(sheetList.size()==0)
		{
			ExcelSheet<One> sheet = new ExcelSheet<One>();
			sheet.setName("全部");
			sheet.setDataList(new ArrayList<One>());
			sheetList.add(sheet);
		}
		
		book.setSheetList(sheetList);
		
		return book;
	}
	
}
